<?php

namespace addons\helper\controller\wxapp;

use addons\helper\controller\wxapp\Base;
use fast\Random;

class Dashboard extends Base
{
    public function _initialize()
    {
        parent::_initialize();
        $this -> year = $this -> request -> get('year',date('Y'));
        $this->model = new \app\admin\model\Project([],$this -> year);
        $this->tenmillion_model = new \app\admin\model\Tenmillionproject();
        $this->project_model = new \app\admin\model\Project();
    }
    public function index()
    {


        // 2021签约
        $nan = $this -> model -> query("select SUM(IF(investment_total >= 1,1,0)) as 'projectcount',
            sum(IF(investment_total >= 1,investment_total,0)) as 'investment_total',
            SUM(IF(investment_total >= 1,1,0)) as 'over1count',
            SUM(IF(investment_total >= 1,investment_total,0)) as 'over1amount',

            SUM(IF(investment_total >= 5,1,0)) as 'over5count',
            SUM(IF(investment_total >= 5,investment_total,0)) as 'over5amount',
            SUM(IF(investment_total >= 30,1,0)) as 'over30count',
            SUM(IF(investment_total >= 30,investment_total,0)) as 'over30amount',
            SUM(IF(investment_total >= 60,1,0)) as 'over60count',
            SUM(IF(investment_total >= 60,investment_total,0)) as 'over60amount',
            SUM(IF(investment_total >= 100,1,0)) as 'over100count',
            SUM(IF(investment_total >= 100,investment_total,0)) as 'over100amount',

            sum(IF(FIND_IN_SET(1,chargedep_ids),1,0)) as 'chengyangcount',
            sum(IF(FIND_IN_SET(1,chargedep_ids),IF(investment_total > 0,investment_total,0),0)) as 'chengyangamount',
            sum(IF(FIND_IN_SET(2,chargedep_ids),1,0)) as 'liutingcount',
            sum(IF(FIND_IN_SET(2,chargedep_ids),IF(investment_total > 0,investment_total,0),0)) as 'liutingamount',
            sum(IF(FIND_IN_SET(3,chargedep_ids),1,0)) as 'xiazhuangcount',
            sum(IF(FIND_IN_SET(3,chargedep_ids),IF(investment_total > 0,investment_total,0),0)) as 'xiazhuangamount',
            sum(IF(FIND_IN_SET(4,chargedep_ids),1,0)) as 'xifuzhencount',
            sum(IF(FIND_IN_SET(4,chargedep_ids),IF(investment_total > 0,investment_total,0),0)) as 'xifuzhenamount',
            sum(IF(FIND_IN_SET(5,chargedep_ids),1,0)) as 'jihongtancount',
            sum(IF(FIND_IN_SET(5,chargedep_ids),IF(investment_total > 0,investment_total,0),0)) as 'jihongtanamount',
            sum(IF(FIND_IN_SET(6,chargedep_ids),1,0)) as 'shangmacount',
            sum(IF(FIND_IN_SET(6,chargedep_ids),IF(investment_total > 0,investment_total,0),0)) as 'shangmaamount',
            sum(IF(FIND_IN_SET(7,chargedep_ids),1,0)) as 'hetaocount',
            sum(IF(FIND_IN_SET(7,chargedep_ids),IF(investment_total > 0,investment_total,0),0)) as 'hetaoamount',
            sum(IF(FIND_IN_SET(8,chargedep_ids),1,0)) as 'hongdaocount',
            sum(IF(FIND_IN_SET(8,chargedep_ids),investment_total,0)) as 'hongdaoamount',
            sum(IF(FIND_IN_SET(9,chargedep_ids),1,0)) as 'zhaoshangcount',
            sum(IF(FIND_IN_SET(9,chargedep_ids),investment_total,0)) as 'zhaoshangamount',
            sum(IF(FIND_IN_SET(13,chargedep_ids),1,0)) as 'guidaocount',
            sum(IF(FIND_IN_SET(13,chargedep_ids),investment_total,0)) as 'guidaoamount',
            sum(IF(FIND_IN_SET(26,chargedep_ids),1,0)) as 'shizhengcount',
            sum(IF(FIND_IN_SET(26,chargedep_ids),investment_total,0)) as 'shizhengamount',
            sum(IF(FIND_IN_SET(27,chargedep_ids),1,0)) as 'fazhancount',
            sum(IF(FIND_IN_SET(27,chargedep_ids),investment_total,0)) as 'fazhanamount',
            sum(IF(FIND_IN_SET(28,chargedep_ids),1,0)) as 'kaifacount',
            sum(IF(FIND_IN_SET(28,chargedep_ids),investment_total,0)) as 'kaifaamount',
            sum(IF(FIND_IN_SET(29,chargedep_ids),1,0)) as 'dongchecount',
            sum(IF(FIND_IN_SET(29,chargedep_ids),investment_total,0)) as 'dongcheamount',
            sum(IF(FIND_IN_SET(30,chargedep_ids),1,0)) as 'chuangxincount',
            sum(IF(FIND_IN_SET(30,chargedep_ids),investment_total,0)) as 'chuangxinamount',
            sum(IF(FIND_IN_SET(31,chargedep_ids),1,0)) as 'baoshuicount',
            sum(IF(FIND_IN_SET(31,chargedep_ids),investment_total,0)) as 'baoshuiamount',
            sum(IF(FIND_IN_SET(34,chargedep_ids),1,0)) as 'huanhaicount',
            sum(IF(FIND_IN_SET(34,chargedep_ids),investment_total,0)) as 'huanhaiamount',
            sum(IF(FIND_IN_SET(45,chargedep_ids),1,0)) as 'nongyefazhancount',
            sum(IF(FIND_IN_SET(45,chargedep_ids),investment_total,0)) as 'nongyefazhanamount',
            sum(IF(FIND_IN_SET(50,chargedep_ids),1,0)) as 'konggucount',
            sum(IF(FIND_IN_SET(50,chargedep_ids),investment_total,0)) as 'kongguamount',
            sum(IF(FIND_IN_SET(51,chargedep_ids),1,0)) as 'xinchengcount',
            sum(IF(FIND_IN_SET(51,chargedep_ids),investment_total,0)) as 'xinchengamount',
            sum(IF(FIND_IN_SET(52,chargedep_ids),1,0)) as 'hongjiancount',
            sum(IF(FIND_IN_SET(52,chargedep_ids),investment_total,0)) as 'hongjianamount',

            sum(IF(FIND_IN_SET(10,chargedep_ids),1,0)) as 'qukejicount',
            sum(IF(FIND_IN_SET(10,chargedep_ids),investment_total,0)) as 'qukejiamount',
            sum(IF(FIND_IN_SET(11,chargedep_ids),1,0)) as 'quxinxicount',
            sum(IF(FIND_IN_SET(11,chargedep_ids),investment_total,0)) as 'quxinxiamount',
            sum(IF(FIND_IN_SET(12,chargedep_ids),1,0)) as 'qulvyoucount',
            sum(IF(FIND_IN_SET(12,chargedep_ids),investment_total,0)) as 'qulvyouamount',
            sum(IF(FIND_IN_SET(14,chargedep_ids),1,0)) as 'qujinrongcount',
            sum(IF(FIND_IN_SET(14,chargedep_ids),investment_total,0)) as 'qujinrongamount',

            sum(IF(FIND_IN_SET(22,chargedep_ids),1,0)) as 'qufazhancount',
            sum(IF(FIND_IN_SET(22,chargedep_ids),investment_total,0)) as 'qufazhanamount',
            sum(IF(FIND_IN_SET(47,chargedep_ids),1,0)) as 'rihancount',
            sum(IF(FIND_IN_SET(47,chargedep_ids),investment_total,0)) as 'rihanamount',
            sum(IF(FIND_IN_SET(37,chargedep_ids),1,0)) as 'qunongyecount',
            sum(IF(FIND_IN_SET(37,chargedep_ids),investment_total,0)) as 'qunongyeamount',

            sum(IF(FIND_IN_SET(45,chargedep_ids),1,0)) as 'yangguangcount',
            sum(IF(FIND_IN_SET(45,chargedep_ids),investment_total,0)) as 'yangguangamount',
            sum(IF(FIND_IN_SET(39,chargedep_ids),1,0)) as 'quweijiancount',
            sum(IF(FIND_IN_SET(39,chargedep_ids),investment_total,0)) as 'quweijianamount',


            sum(case five_industry_id when 1 then 1 else 0 end) as 'gaoduancount',
            sum(IF(five_industry_id = 1,IF(investment_total > 0,investment_total,0),0)) as 'gaoduanamount',
            sum(case five_industry_id when 2 then 1 else 0 end) as 'xinnengyuancount',
            sum(IF(five_industry_id = 2,IF(investment_total > 0,investment_total,0),0)) as 'xinnengyuanamount',
            sum(case five_industry_id when 3 then 1 else 0 end) as 'xinyidaicount',
            sum(IF(five_industry_id = 3,IF(investment_total > 0,investment_total,0),0)) as 'xinyidaiamount',
            sum(case five_industry_id when 4 then 1 else 0 end) as 'shengwucount',
            sum(IF(five_industry_id = 4,IF(investment_total > 0,investment_total,0),0)) as 'shengwuamount',
            sum(case five_industry_id when 5 then 1 else 0 end) as 'xiandaicount',
            sum(IF(five_industry_id = 5,IF(investment_total > 0,investment_total,0),0)) as 'xiandaiamount',
            sum(case five_industry_id when 6 then 1 else 0 end) as 'qitacount',
            sum(IF(five_industry_id = 6,IF(investment_total > 0,investment_total,0),0)) as 'qitaamount',
            sum(IF(FIND_IN_SET(6,trade_category_ids),1,0)) as 'xinjijiancount',
            sum(IF(FIND_IN_SET(6,trade_category_ids),IF(investment_total > 0,investment_total,0),0)) as 'xinjijianamount',
            sum(IF(FIND_IN_SET(7,trade_category_ids),1,0)) as 'hulianwangcount',
            sum(IF(FIND_IN_SET(7,trade_category_ids),IF(investment_total > 0,investment_total,0),0)) as 'hulianwangamount',
            sum(IF(FIND_IN_SET(8,trade_category_ids),1,0)) as 'zongbucount',
            sum(IF(FIND_IN_SET(8,trade_category_ids),IF(investment_total > 0,investment_total,0),0)) as 'zongbuamount',
            sum(IF(FIND_IN_SET(9,trade_category_ids),1,0)) as 'sixincount',
            sum(IF(FIND_IN_SET(9,trade_category_ids),IF(investment_total > 0,investment_total,0),0)) as 'sixinamount',
            sum(IF(FIND_IN_SET(11,trade_category_ids),1,0)) as 'gongyeloucount',
            sum(IF(FIND_IN_SET(11,trade_category_ids),IF(investment_total > 0,investment_total,0),0)) as 'gongyelouamount',
            sum(IF(FIND_IN_SET(10,trade_category_ids),1,0)) as 'qtcount',
            sum(IF(FIND_IN_SET(10,trade_category_ids),IF(investment_total > 0,investment_total,0),0)) as 'qtamount',
            sum(case industry_category_id when 6 then 1 else 0 end) as 'nongyecount',
            sum(case industry_category_id when 7 then 1 else 0 end) as 'zhizaoyecount',
            sum(case industry_category_id when 8 then 1 else 0 end) as 'jianzhuyecount',
            sum(case industry_category_id when 9 then 1 else 0 end) as 'fuwuyecount',
            sum(case industry_category_id when 10 then 1 else 0 end) as 'fangdichancount',
            sum(case industry_category_id when 11 then 1 else 0 end) as 'ronghecount',
            sum(case industry_category_id when 12 then 1 else 0 end) as 'keyancount'

            from nsig_project_new WHERE is_delete =0 and investment_total >= 1 and progress_status_id NOT IN ( 9, 10 )  and id in( select project_id from nsig_project_stage WHERE project_stage_id in(4,5) and project_stage_time>=".strtotime("2021-01-01").")" );
        $nan = $nan[0];

        $seventtime = \fast\Date::unixtime('day', -7);
        $paylist = $createlist = $commentList = [];
        for ($i = 0; $i < 7; $i++)
        {
            $day = date("m-d", $seventtime + ($i * 86400));
            $createlist[$day] = mt_rand(20, 200);
            $paylist[$day] = mt_rand(1, mt_rand(1, $createlist[$day]));
            $commentList[$day] = Random::nozero(3);
        }
        $tabList = ['按投资额划分','按单位划分','按产业划分'];
        $categoryList = model('app\common\model\Category')->getCategoryArray();
        $agreementList = [];
        // foreach ($categoryList as $k => $v) {
        //     $agreementList[$k]['value'] = Random::nozero(3);
        //     $agreementList[$k]['name'] = $v['name'];
        // }
        if($nan['nongyecount']>0)
            $agreementList[] =['name'=>'农业','value'=>$nan['nongyecount']];
        if($nan['zhizaoyecount']>0)
            $agreementList[] =['name'=>'制造业','value'=>$nan['zhizaoyecount']];
        if($nan['jianzhuyecount']>0)
            $agreementList[] =['name'=>'建筑业','value'=>$nan['jianzhuyecount']];
        if($nan['fuwuyecount']>0)
            $agreementList[] =['name'=>'服务业','value'=>$nan['fuwuyecount']];
        if($nan['fangdichancount']>0)
            $agreementList[] =['name'=>'房地产','value'=>$nan['fangdichancount']];
        if($nan['ronghecount']>0)
            $agreementList[] =['name'=>'产城融合','value'=>$nan['ronghecount']];
        if($nan['keyancount']>0)
            $agreementList[] =['name'=>'学校及科研院校','value'=>$nan['keyancount']];


        // 街道排名
        $streetList =  [
            ['id'=> 1, 'name'=>'城阳街道','count'=>$nan['chengyangcount'],'amount' => $nan['chengyangamount']],
            ['id'=> 2, 'name'=>'流亭街道','count'=>$nan['liutingcount'],'amount' => $nan['liutingamount']],
            ['id'=> 3, 'name'=>'夏庄街道','count'=>$nan['xiazhuangcount'],'amount' => $nan['xiazhuangamount']],
            ['id'=> 4, 'name'=>'惜福镇街道','count'=>$nan['xifuzhencount'],'amount' => $nan['xifuzhenamount']],
            ['id'=> 5, 'name'=>'棘洪滩街道','count'=>$nan['jihongtancount'],'amount' => $nan['jihongtanamount']],
            ['id'=> 6, 'name'=>'红岛街道','count'=>$nan['hongdaocount'],'amount' => $nan['hongdaoamount']],
            ['id'=> 7, 'name'=>'上马街道','count'=>$nan['shangmacount'],'amount' => $nan['shangmaamount']],
            ['id'=> 8, 'name'=>'河套街道','count'=>$nan['hetaocount'],'amount' => $nan['hetaoamount']],
        ];
        foreach ($streetList as $key => $value)
        {
            if($value['count']==0)
            {
                unset($streetList[$key]);
            }
        }
        $streetlist_count = array_column($streetList,'count');
        array_multisort($streetlist_count,SORT_DESC,$streetList);

        // 区直企业排名
        $companyList = [
            ['id'=> 29, 'name'=>'动车小镇投资公司','count'=>$nan['dongchecount'],'amount' => $nan['dongcheamount']],
            ['id'=> 30, 'name'=>'城阳阳光创新投资公司','count'=>$nan['chuangxincount'],'amount' => $nan['chuangxinamount']],
            ['id'=> 45, 'name'=>'阳光农业发展投资公司','count'=>$nan['nongyefazhancount'],'amount' => $nan['nongyefazhanamount']],
            ['id'=> 52, 'name'=>'青岛红建投资有限公司','count'=>$nan['hongjiancount'],'amount' => $nan['hongjianamount']],

            ['id'=> 26, 'name'=>'城阳市政投资集团','count'=>$nan['shizhengcount'],'amount' => $nan['shizhengamount']],
            ['id'=> 27, 'name'=>'城阳城市发展集团有限公司','count'=>$nan['fazhancount'],'amount' => $nan['fazhanamount']],
            ['id'=> 28, 'name'=>'城阳开发投资集团有限公司','count'=>$nan['kaifacount'],'amount' => $nan['kaifaamount']],
            ['id'=> 50, 'name'=>'城阳区阳光城阳控股集团有限公司','count'=>$nan['konggucount'],'amount' => $nan['kongguamount']],
            ['id'=> 51, 'name'=>'青岛环海新城投资有限公司','count'=>$nan['xinchengcount'],'amount' => $nan['xinchengamount']],
            
        ];
        foreach ($companyList as $key => $value)
        {
            if($value['count']==0)
            {
                unset($companyList[$key]);
            }
        }
        $companyList_count = array_column($companyList,'count');
        array_multisort($companyList_count,SORT_DESC,$companyList);

        // 区直部门排名
        $departList = [
            ['id'=> 13, 'name'=>'轨道交通产业示范区','count'=>$nan['guidaocount'],'amount' => $nan['guidaoamount']],
            ['id'=> 31, 'name'=>'胶州湾综合保税区','count'=>$nan['baoshuicount'],'amount' => $nan['baoshuiamount']],
            ['id'=> 34, 'name'=>'环海经济开发区管委会','count'=>$nan['huanhaicount'],'amount' => $nan['huanhaiamount']],
        ];
        foreach ($departList as $key => $value)
        {
            if($value['count']==0)
            {
                unset($departList[$key]);
            }
        }
        $departList_count = array_column($departList,'count');
        array_multisort($departList_count,SORT_DESC,$departList);


        $industryList = [
            ['id' => 1, 'name'=>'高端装备制造','count'=>$nan['gaoduancount'],'value'=>$nan['gaoduancount'],'amount' => $nan['gaoduanamount']],
            ['id' => 2, 'name'=>'新能源新材料','count'=>$nan['xinnengyuancount'],'value'=>$nan['xinnengyuancount'],'amount' => $nan['xinnengyuanamount']],
            ['id' => 3, 'name'=>'新一代信息技术','count'=>$nan['xinyidaicount'],'value'=>$nan['xinyidaicount'],'amount' => $nan['xinyidaiamount']],
            ['id' => 4, 'name'=>'生物医药健康','count'=>$nan['shengwucount'],'value'=>$nan['shengwucount'],'amount' => $nan['shengwuamount']],
            ['id' => 5, 'name'=>'现代服务业','count'=>$nan['xiandaicount'],'value'=>$nan['xiandaicount'],'amount' => $nan['xiandaiamount']],
            ['id' => 6, 'name'=>'其他','count'=>$nan['qitacount'],'value'=>$nan['qitacount'],'amount' => $nan['qitaamount']],
        ];

        $industryCategoryList = [
            ['id' => 1,'name'=>'新基建','value'=>$nan['xinjijiancount']],
            ['id' => 2 ,'name'=>'工业互联网','value'=>$nan['hulianwangcount']],
            ['id' => 3 ,'name'=>'总部','value'=>$nan['zongbucount']],
            ['id' => 4 ,'name'=>'四新','value'=>$nan['sixincount']],
            ['id' => 5 ,'name'=>'工业楼宇','value'=>$nan['gongyeloucount']],
            ['id' => 6 ,'name'=>'其他','value'=>$nan['qtcount']]
        ];

        $tenmillion_allCount = $this->tenmillion_model -> count();
        $tenmillion_streetData = $this->tenmillion_model -> field('street_name,street_id,count(*) as count') -> group('street_id') -> select();
        $tenmillion_overBillion = $this->tenmillion_model -> field('count(*) as count,sum(investment_total) as total') -> where(['investment_total' => ['egt',10000]]) -> find();
        $tenmillion_streetname = [];
        $tenmillion_streetvalue = [];
        foreach ($tenmillion_streetData as $key => $value) {
            $tenmillion_streetname[] = $value['street_name'];
            $tenmillion_streetvalue[] = $value['count'];
        }


        $industry_realmData = $this -> model -> query("select industry_realm_name,industry_realm_id,count(*) as count
            from nsig_project_new WHERE is_delete =0 and progress_status_id<9  and id in( select project_id from nsig_project_stage WHERE project_stage_id in(4,5) and project_stage_time>=".strtotime("2021-01-01").") group by industry_realm_id order by industry_realm_id" );
        // $industry_realmData = $this->project_model ->where('is_delete=0')-> field('industry_realm_name,industry_realm_id,count(*) as count') -> group('industry_realm_id') ->order('industry_realm_id')-> select();

        $industry_realmname = [];
        $industry_realmvalue = [];
        foreach ($industry_realmData as $key => $v) {
            if($v['industry_realm_id']>0)
            {
                $industry_realmname[] = $v['industry_realm_name'];
                $industry_realmvalue[] = $v['count'];
            } 
        }

        //2021在谈
        $dis_tabList = ['按单位划分','按五大产业划分'];
        $nan1 = $this -> model -> query("select count(*) as 'projectcount',
            sum(IF(investment_total > 0,investment_total,0)) as 'investment_total',
            sum(IF(FIND_IN_SET(1,chargedep_ids),1,0)) as 'chengyangcount',
            sum(IF(FIND_IN_SET(1,chargedep_ids),IF(investment_total > 0,investment_total,0),0)) as 'chengyangamount',
            sum(IF(FIND_IN_SET(2,chargedep_ids),1,0)) as 'liutingcount',
            sum(IF(FIND_IN_SET(2,chargedep_ids),IF(investment_total > 0,investment_total,0),0)) as 'liutingamount',
            sum(IF(FIND_IN_SET(3,chargedep_ids),1,0)) as 'xiazhuangcount',
            sum(IF(FIND_IN_SET(3,chargedep_ids),IF(investment_total > 0,investment_total,0),0)) as 'xiazhuangamount',
            sum(IF(FIND_IN_SET(4,chargedep_ids),1,0)) as 'xifuzhencount',
            sum(IF(FIND_IN_SET(4,chargedep_ids),IF(investment_total > 0,investment_total,0),0)) as 'xifuzhenamount',
            sum(IF(FIND_IN_SET(5,chargedep_ids),1,0)) as 'jihongtancount',
            sum(IF(FIND_IN_SET(5,chargedep_ids),IF(investment_total > 0,investment_total,0),0)) as 'jihongtanamount',
            sum(IF(FIND_IN_SET(6,chargedep_ids),1,0)) as 'shangmacount',
            sum(IF(FIND_IN_SET(6,chargedep_ids),IF(investment_total > 0,investment_total,0),0)) as 'shangmaamount',
            sum(IF(FIND_IN_SET(7,chargedep_ids),1,0)) as 'hetaocount',
            sum(IF(FIND_IN_SET(7,chargedep_ids),IF(investment_total > 0,investment_total,0),0)) as 'hetaoamount',
            sum(IF(FIND_IN_SET(8,chargedep_ids),1,0)) as 'hongdaocount',
            sum(IF(FIND_IN_SET(8,chargedep_ids),investment_total,0)) as 'hongdaoamount',
            sum(IF(FIND_IN_SET(9,chargedep_ids),1,0)) as 'zhaoshangcount',
            sum(IF(FIND_IN_SET(9,chargedep_ids),investment_total,0)) as 'zhaoshangamount',
            sum(IF(FIND_IN_SET(13,chargedep_ids),1,0)) as 'guidaocount',
            sum(IF(FIND_IN_SET(13,chargedep_ids),investment_total,0)) as 'guidaoamount',
            sum(IF(FIND_IN_SET(26,chargedep_ids),1,0)) as 'shizhengcount',
            sum(IF(FIND_IN_SET(26,chargedep_ids),investment_total,0)) as 'shizhengamount',
            sum(IF(FIND_IN_SET(27,chargedep_ids),1,0)) as 'fazhancount',
            sum(IF(FIND_IN_SET(27,chargedep_ids),investment_total,0)) as 'fazhanamount',
            sum(IF(FIND_IN_SET(28,chargedep_ids),1,0)) as 'kaifacount',
            sum(IF(FIND_IN_SET(28,chargedep_ids),investment_total,0)) as 'kaifaamount',
            sum(IF(FIND_IN_SET(29,chargedep_ids),1,0)) as 'dongchecount',
            sum(IF(FIND_IN_SET(29,chargedep_ids),investment_total,0)) as 'dongcheamount',
            sum(IF(FIND_IN_SET(30,chargedep_ids),1,0)) as 'chuangxincount',
            sum(IF(FIND_IN_SET(30,chargedep_ids),investment_total,0)) as 'chuangxinamount',
            sum(IF(FIND_IN_SET(31,chargedep_ids),1,0)) as 'baoshuicount',
            sum(IF(FIND_IN_SET(31,chargedep_ids),investment_total,0)) as 'baoshuiamount',
            sum(IF(FIND_IN_SET(34,chargedep_ids),1,0)) as 'huanhaicount',
            sum(IF(FIND_IN_SET(34,chargedep_ids),investment_total,0)) as 'huanhaiamount',
            sum(IF(FIND_IN_SET(45,chargedep_ids),1,0)) as 'nongyefazhancount',
            sum(IF(FIND_IN_SET(45,chargedep_ids),investment_total,0)) as 'nongyefazhanamount',
            sum(IF(FIND_IN_SET(50,chargedep_ids),1,0)) as 'konggucount',
            sum(IF(FIND_IN_SET(50,chargedep_ids),investment_total,0)) as 'kongguamount',
            sum(IF(FIND_IN_SET(51,chargedep_ids),1,0)) as 'xinchengcount',
            sum(IF(FIND_IN_SET(51,chargedep_ids),investment_total,0)) as 'xinchengamount',
            sum(IF(FIND_IN_SET(52,chargedep_ids),1,0)) as 'hongjiancount',
            sum(IF(FIND_IN_SET(52,chargedep_ids),investment_total,0)) as 'hongjianamount',
            
            sum(IF(FIND_IN_SET(10,chargedep_ids),1,0)) as 'qukejicount',
            sum(IF(FIND_IN_SET(10,chargedep_ids),investment_total,0)) as 'qukejiamount',
            sum(IF(FIND_IN_SET(11,chargedep_ids),1,0)) as 'quxinxicount',
            sum(IF(FIND_IN_SET(11,chargedep_ids),investment_total,0)) as 'quxinxiamount',
            sum(IF(FIND_IN_SET(12,chargedep_ids),1,0)) as 'qulvyoucount',
            sum(IF(FIND_IN_SET(12,chargedep_ids),investment_total,0)) as 'qulvyouamount',
            sum(IF(FIND_IN_SET(14,chargedep_ids),1,0)) as 'qujinrongcount',
            sum(IF(FIND_IN_SET(14,chargedep_ids),investment_total,0)) as 'qujinrongamount',

            sum(IF(FIND_IN_SET(22,chargedep_ids),1,0)) as 'qufazhancount',
            sum(IF(FIND_IN_SET(22,chargedep_ids),investment_total,0)) as 'qufazhanamount',
            sum(IF(FIND_IN_SET(47,chargedep_ids),1,0)) as 'rihancount',
            sum(IF(FIND_IN_SET(47,chargedep_ids),investment_total,0)) as 'rihanamount',
            sum(IF(FIND_IN_SET(37,chargedep_ids),1,0)) as 'qunongyecount',
            sum(IF(FIND_IN_SET(37,chargedep_ids),investment_total,0)) as 'qunongyeamount',

            sum(IF(FIND_IN_SET(45,chargedep_ids),1,0)) as 'yangguangcount',
            sum(IF(FIND_IN_SET(45,chargedep_ids),investment_total,0)) as 'yangguangamount',
            sum(IF(FIND_IN_SET(39,chargedep_ids),1,0)) as 'quweijiancount',
            sum(IF(FIND_IN_SET(39,chargedep_ids),investment_total,0)) as 'quweijianamount',


            sum(case five_industry_id when 1 then 1 else 0 end) as 'gaoduancount',
            sum(case five_industry_id when 2 then 1 else 0 end) as 'xinnengyuancount',
            sum(case five_industry_id when 3 then 1 else 0 end) as 'xinyidaicount',
            sum(case five_industry_id when 4 then 1 else 0 end) as 'shengwucount',
            sum(case five_industry_id when 5 then 1 else 0 end) as 'xiandaicount',
            sum(case five_industry_id when 6 then 1 else 0 end) as 'qitacount'
        from nsig_project_new p where p.is_delete =0 and p.progress_status_id =1");
        $nan1 = $nan1[0];

        $dis_streetList =  [
            ['id'=> 1,'name'=>'城阳街道','count'=>$nan1['chengyangcount'],'amount' => $nan1['chengyangamount']],
            ['id'=> 2,'name'=>'流亭街道','count'=>$nan1['liutingcount'],'amount' => $nan1['liutingamount']],
            ['id'=> 3,'name'=>'夏庄街道','count'=>$nan1['xiazhuangcount'],'amount' => $nan1['xiazhuangamount']],
            ['id'=> 4,'name'=>'惜福镇街道','count'=>$nan1['xifuzhencount'],'amount' => $nan1['xifuzhenamount']],
            ['id'=> 5,'name'=>'棘洪滩街道','count'=>$nan1['jihongtancount'],'amount' => $nan1['jihongtanamount']],
            ['id'=> 6,'name'=>'红岛街道','count'=>$nan1['hongdaocount'],'amount' => $nan1['hongdaoamount']],
            ['id'=> 7,'name'=>'上马街道','count'=>$nan1['shangmacount'],'amount' => $nan1['shangmaamount']],
            ['id'=> 8,'name'=>'河套街道','count'=>$nan1['hetaocount'],'amount' => $nan1['hetaoamount']],
        ];

        $streetlist_count = array_column($dis_streetList,'count');
        array_multisort($streetlist_count,SORT_DESC,$dis_streetList);



        // 区直企业排名
        $dis_companyList = [
            ['id'=> 29, 'name'=>'动车小镇投资公司','count'=>$nan1['dongchecount'],'amount' => $nan1['dongcheamount']],
            ['id'=> 30, 'name'=>'城阳阳光创新投资公司','count'=>$nan1['chuangxincount'],'amount' => $nan1['chuangxinamount']],
            ['id'=> 45, 'name'=>'阳光农业发展投资公司','count'=>$nan1['nongyefazhancount'],'amount' => $nan1['nongyefazhanamount']],
            ['id'=> 52, 'name'=>'青岛红建投资有限公司','count'=>$nan1['hongjiancount'],'amount' => $nan1['hongjianamount']],
            ['id'=> 26, 'name'=>'城阳市政投资集团','count'=>$nan1['shizhengcount'],'amount' => $nan1['shizhengamount']],
            ['id'=> 27, 'name'=>'城阳城市发展集团有限公司','count'=>$nan1['fazhancount'],'amount' => $nan1['fazhanamount']],
            ['id'=> 28, 'name'=>'城阳开发投资集团有限公司','count'=>$nan1['kaifacount'],'amount' => $nan1['kaifaamount']],
            ['id'=> 50, 'name'=>'城阳区阳光城阳控股集团有限公司','count'=>$nan1['konggucount'],'amount' => $nan1['kongguamount']],
            ['id'=> 51, 'name'=>'青岛环海新城投资有限公司','count'=>$nan1['xinchengcount'],'amount' => $nan1['xinchengamount']],
        ];
        foreach ($dis_companyList as $key => $value)
        {
            if($value['count']==0)
            {
                unset($dis_companyList[$key]);
            }
        }
        $dis_companyList_count = array_column($dis_companyList,'count');
        array_multisort($dis_companyList_count,SORT_DESC,$dis_companyList);


        // 区直部门排名
        $dis_departList = [
            ['id'=> 13, 'name'=>'轨道交通产业示范区','count'=>$nan1['guidaocount'],'amount' => $nan1['guidaoamount']],
            ['id'=> 31, 'name'=>'胶州湾综合保税区','count'=>$nan1['baoshuicount'],'amount' => $nan1['baoshuiamount']],
            ['id'=> 34, 'name'=>'环海经济开发区管委会','count'=>$nan1['huanhaicount'],'amount' => $nan1['huanhaiamount']],
        ];
        foreach ($dis_departList as $key => $value)
        {
            if($value['count']==0)
            {
                unset($dis_departList[$key]);
            }
        }
        $dis_departList_count = array_column($dis_departList,'count');
        array_multisort($dis_departList_count,SORT_DESC,$dis_departList);


        $dis_industryList = [
            ['id' => 1,'name'=>'高端装备制造','count'=>$nan1['gaoduancount']],
            ['id' => 2,'name'=>'新能源新材料','count'=>$nan1['xinnengyuancount']],
            ['id' => 3,'name'=>'新一代信息技术','count'=>$nan1['xinyidaicount']],
            ['id' => 4,'name'=>'生物医药健康','count'=>$nan1['shengwucount']],
            ['id' => 5,'name'=>'现代服务业','count'=>$nan1['xiandaicount']],
            ['id' => 6,'name'=>'其他','count'=>$nan1['qitacount']],
        ];

        // 项目当前阶段统计
        $project_arr = [
            ['name'=>'在谈','id'=>1,'value'=>0,'percent'=>0],
            ['name'=>'运营','id'=>2,'value'=>0,'percent'=>0],
            ['name'=>'准入','id'=>3,'value'=>0,'percent'=>0],
            ['name'=>'框架协议','id'=>4,'value'=>0,'percent'=>0],
            ['name'=>'正式协议','id'=>5,'value'=>0,'percent'=>0],
            ['name'=>'注册','id'=>6,'value'=>0,'percent'=>0],
            ['name'=>'拿地','id'=>7,'value'=>0,'percent'=>0],
            ['name'=>'开工','id'=>8,'value'=>0,'percent'=>0],
            ['name'=>'暂缓','id'=>9,'value'=>0,'percent'=>0],
            ['name'=>'终止','id'=>10,'value'=>0,'percent'=>0],
        ];


        // $nan = $this -> model -> query("select count(*) as 'projectcount',
        //     sum(IF(investment_total > 0,investment_total,0)) as 'investment_total'
        // from nsig_project_new p where p.is_delete =0 and p.progress_status_id =1");


        // 统计图
        $project_arr_list = [];

        // 项目有效总数
        $total = $this -> model -> query("SELECT COUNT(*) as zong FROM nsig_project_new WHERE is_delete=0 and progress_status_id<9");
        $total = $total[0];
        $project_total = $total['zong'];

        foreach($project_arr as $ke =>$val)
        {
            // if($val['id']==10){ //终止
            //     $project_list = $this -> model -> query("select id,status FROM nsig_project_new WHERE is_delete=0 and (progress_status_id=9 or progress_status_id=10)  GROUP BY name ");
            // }else{
                $project_list = $this -> model -> query("select id,status FROM nsig_project_new WHERE is_delete=0 and progress_status_id=".$val['id']." GROUP BY name ");
            // }
            if(count($project_list)>0)
            {
                $project_arr[$ke]['value'] = count($project_list); 
                // $project_arr[$ke]['percent'] = number_format(count($project_list)/$project_total*100)."%"; 
            }
            $project_arr_list[$ke] = ['name'=>$project_arr[$ke]['name'],'value'=>$project_arr[$ke]['value']];
        }
        // 追加其他比例
        $qita = $this -> model -> query("SELECT COUNT(*) as zong FROM nsig_project_new WHERE is_delete=0 and progress_status_id NOT IN(1,4,5,6,2,9,10)");
        $qita = $qita[0];
        array_push($project_arr_list,["name"=>'其他','value'=>$qita['zong']]);


        // 功能区汇总：
        $chargedep_arr = [
            ['name'=>'街道','amount'=>0,'investment_total'=>0,'filter'=>'1,2,3,4,5,6,7,8'],
            ['name'=>'区直企业','amount'=>0,'investment_total'=>0,'filter'=>'26,27,28,29,30,45,50,51,52'],
            // ['name'=>'功能区','amount'=>0,'investment_total'=>0,'filter'=>'10,11,12,14,15,16,17,18,19,20,21,22,23,24,25,32,33,36,37,38,39,40,42,44,45,46,47,48,49,53,54,55'],
            
            ['name'=>'功能区','amount'=>0,'investment_total'=>0,'filter'=>'13,31,34'],

        ];

        $jiedao = $this -> model -> query("select count(*) as 'projectcount',
            sum(IF(investment_total > 0,investment_total,0)) as 'investment_total'
            from nsig_project_new WHERE is_delete =0  and investment_total>=1 and progress_status_id NOT IN (9,10) and chargedep_ids in(1,2,3,4,5,6,7,8) and id in( select project_id from nsig_project_stage WHERE project_stage_id in(4,5) and project_stage_time>=".strtotime("2021-01-01").")" );
        $jiedao = $jiedao[0];
        $chargedep_arr[0]['amount']=$jiedao['projectcount'];
        $chargedep_arr[0]['investment_total']=$jiedao['investment_total']? $jiedao['investment_total'] :0;

        $qiye = $this -> model -> query("select count(*) as 'projectcount',
            sum(IF(investment_total > 0,investment_total,0)) as 'investment_total'
            from nsig_project_new WHERE is_delete =0 and investment_total>=1 and progress_status_id NOT IN (9,10) and chargedep_ids in(26,27,28,29,30,45,50,51,52) and id in( select project_id from nsig_project_stage WHERE project_stage_id in(4,5) and project_stage_time>=".strtotime("2021-01-01").")" );
        $qiye = $qiye[0];
        $chargedep_arr[1]['amount']=$qiye['projectcount'];
        $chargedep_arr[1]['investment_total']=$qiye['investment_total'] ? $qiye['investment_total'] :0;

        $bumen = $this -> model -> query("select count(*) as 'projectcount',
            sum(IF(investment_total > 0,investment_total,0)) as 'investment_total'
            from nsig_project_new WHERE is_delete =0 and investment_total>=1 and progress_status_id NOT IN (9,10) and chargedep_ids in(13,31,34) and id in( select project_id from nsig_project_stage WHERE project_stage_id in(4,5) and project_stage_time>=".strtotime("2021-01-01").")" );
        $bumen = $bumen[0];
        $chargedep_arr[2]['amount']=$bumen['projectcount'];
        $chargedep_arr[2]['investment_total']=$bumen['investment_total'] ? $bumen['investment_total'] :0;



         // 功能区汇总：
        $chargedep_dis_arr = [
            ['name'=>'街道','amount'=>0,'investment_total'=>0,'filter'=>'1,2,3,4,5,6,7,8'],
            ['name'=>'区直企业','amount'=>0,'investment_total'=>0,'filter'=>'26,27,28,29,30,45,50,51,52'],
            // ['name'=>'功能区','amount'=>0,'investment_total'=>0,'filter'=>'10,11,12,14,15,16,17,18,19,20,21,22,23,24,25,32,33,36,37,38,39,40,42,44,45,46,47,48,49,53,54,55'],
            ['name'=>'功能区','amount'=>0,'investment_total'=>0,'filter'=>'13,31,34'],
        ];


        $jiedao1 = $this -> model -> query("select count(*) as 'projectcount',
            sum(IF(investment_total > 0,investment_total,0)) as 'investment_total'
            from nsig_project_new WHERE is_delete =0 and progress_status_id=1 and chargedep_ids in(1,2,3,4,5,6,7,8) " );
        $jiedao1 = $jiedao1[0];
        $chargedep_dis_arr[0]['amount']=$jiedao1['projectcount'];
        $chargedep_dis_arr[0]['investment_total']=$jiedao1['investment_total']? $jiedao1['investment_total'] :0;

        $qiye1 = $this -> model -> query("select count(*) as 'projectcount',
            sum(IF(investment_total > 0,investment_total,0)) as 'investment_total'
            from nsig_project_new WHERE is_delete =0 and progress_status_id=1 and chargedep_ids in(26,27,28,29,30,45,50,51,52) " );
        $qiye1 = $qiye1[0];
        $chargedep_dis_arr[1]['amount']=$qiye1['projectcount'];
        $chargedep_dis_arr[1]['investment_total']=$qiye1['investment_total'] ? $qiye1['investment_total'] :0;

        $bumen1 = $this -> model -> query("select count(*) as 'projectcount',
            sum(IF(investment_total > 0,investment_total,0)) as 'investment_total'
            from nsig_project_new WHERE is_delete =0 and progress_status_id=1 and chargedep_ids in(13,31,34)" );
        $bumen1 = $bumen1[0];
        $chargedep_dis_arr[2]['amount']=$bumen1['projectcount'];
        $chargedep_dis_arr[2]['investment_total']=$bumen1['investment_total'] ? $bumen1['investment_total'] :0;

        $data = [
            'tabList'          => $tabList,
            'project_arr_list'          => $project_arr_list,
            'project'          => $project_arr,
            'project_total'    => $project_total,
            'dis_tablist'      => $dis_tabList,
            'dis_companyList'  => $dis_companyList,
            'dis_departList'   => $dis_departList,
            'chargedep_arr'    => $chargedep_arr,
            'chargedep_dis_arr'=>$chargedep_dis_arr,

            'signedcount'        => $nan['projectcount'],
            'investment_total'       => $nan['investment_total'],
            'discusscount' => $nan1['projectcount'],
            // 'discusscount' => 160,
            'discuss_total_count' => $nan1['investment_total'],
            'over1count'       => $nan['over1count'],
            'over1amount'       => $nan['over1amount'],
            'over5count' => $nan['over5count'],
            'over5amount' => $nan['over5amount'],
            'over30count'      => $nan['over30count'],
            'over30amount'      => $nan['over30amount'],
            'over60count'      => $nan['over60count'],
            'over60amount'      => $nan['over60amount'],
            'over100count'    => $nan['over100count'],
            'over100amount'    => $nan['over100amount'],

            'totaluser'        => 35200,
            'totalviews'       => 219390,
            'totalorder'       => 32143,
            'totalorderamount' => 174800,
            'totalprofit'      => 98738,
            'totalcategory'    => 39,
            'totalattachment'  => 3988,
            'todayuserlogin'   => 321,
            'todayusersignup'  => 430,
            'todayorder'       => 2324,
            'unsettleorder'    => 132,
            'sevendnu'         => '80%',
            'sevendau'         => '32%',
            'paylist'          => $paylist,
            'createlist'       => $createlist,
            'streetList'       => $streetList,
            'dis_streetlist'   => $dis_streetList,
            'departList'       => $departList,
            'companyList'      => $companyList,
            'industryList'     => $industryList,
            'dis_industrylist' => $dis_industryList,
            'agreementList'    => $agreementList,
            'industryCategoryList'      => $industryCategoryList,
            'commentList'      => $commentList,
            'tenmillion_streetname' => $tenmillion_streetname,
            'tenmillion_streetvalue' => $tenmillion_streetvalue,
            'industry_realmname' => $industry_realmname,
            'industry_realmvalue' => $industry_realmvalue,

            'tenmillion_allCount' => $tenmillion_allCount,
            'tenmillion_overBillion_count' => $tenmillion_overBillion['count'],
            'tenmillion_overBillion_total' => $tenmillion_overBillion['total'],
            'project_stage_start' => "2021-01-01 00:00:00"." - "."2021-12-31 23:59:59"

        ];
        $this->success('ok', $data);

        return $this->view->fetch();
    }
    public function index2()
    {
        return $this->view->fetch();
    }

}
